﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Model
{
   public class UserInnerJoinCampaign
    {

        private DBUtil DB;
        public string strConn { get; set; }
        public int user_id { get; set; }
        public int user_type_id { get; set; }
        public string username { get; set; }
        public string password { get; set; }
        public int title_th { get; set; }
        public string fname_th { get; set; }
        public string lname_th { get; set; }
        public int title_en { get; set; }
        public string fname_en { get; set; }
        public string lname_en { get; set; }
        public string id_card { get; set; }
        public string tag { get; set; }
        public string birth_date { get; set; }
        public string face_image { get; set; }
        public string gender { get; set; }
        public string email { get; set; }
        public string telephone { get; set; }
        public string mobile { get; set; }
        public string role { get; set; }
        public string lang { get; set; }
        public string create_date { get; set; }
        public int create_by { get; set; }
        public string update_date { get; set; }
        public int update_by { get; set; }
        public int site_id { get; set; }

        public int user_campaign_id { get; set; } 
        public int product_id { get; set; }
        public int campaign_id { get; set; }
        public int member_type_id { get; set; }
        public int payment_detail_id { get; set; }
        public string barcode { get; set; }
        public string RFID { get; set; }
        public string reference_no { get; set; }
        public string start_date { get; set; }
        public string expire_date { get; set; }
        public string status { get; set; }

        public UserInnerJoinCampaign()
        {
          this.DB = new DBUtil();
          DB.strConn = strConn;
        }

        public DataTable getByObject(UserInnerJoinCampaign obj)
        {

            DataTable dt = new DataTable();
            this.DB = new DBUtil();
            DB.strConn = strConn;

            string sql = "SELECT * FROM tcdc_mst_user  as u  "+
                         "inner join tcdc_trn_user_campaign uc on u.user_id=uc.user_id " +
                         "WHERE 1 = 1 ";

            if (obj.user_id != null &&
               obj.user_id.ToString() != null &&
               obj.user_id.ToString() != "0")
            {
                sql += "AND u.user_id LIKE '%" + obj.user_id + "%' ";
            }
            if (obj.user_type_id != null &&
               obj.user_type_id.ToString() != null &&
               obj.user_type_id.ToString() != "0")
            {
                sql += "AND u.user_type_id LIKE '%" + obj.user_type_id + "%' ";
            }
            if (obj.username != null &&
               obj.username.ToString() != null &&
               obj.username.ToString() != "0")
            {
                sql += "AND u.username LIKE '%" + obj.username + "%' ";
            }
            if (obj.password != null &&
               obj.password.ToString() != null &&
               obj.password.ToString() != "0")
            {
                sql += "AND u.password LIKE '%" + obj.password + "%' ";
            }
            if (obj.title_th != null &&
               obj.title_th.ToString() != null &&
               obj.title_th.ToString() != "0")
            {
                sql += "AND u.title_th LIKE '%" + obj.title_th + "%' ";
            }
            if (obj.fname_th != null &&
               obj.fname_th.ToString() != null &&
               obj.fname_th.ToString() != "0")
            {
                sql += "AND u.fname_th LIKE '%" + obj.fname_th + "%' ";
            }
            if (obj.lname_th != null &&
               obj.lname_th.ToString() != null &&
               obj.lname_th.ToString() != "0")
            {
                sql += "AND u.lname_th LIKE '%" + obj.lname_th + "%' ";
            }
            if (obj.title_en != null &&
               obj.title_en.ToString() != null &&
               obj.title_en.ToString() != "0")
            {
                sql += "AND u.title_en LIKE '%" + obj.title_en + "%' ";
            }
            if (obj.fname_en != null &&
               obj.fname_en.ToString() != null &&
               obj.fname_en.ToString() != "0")
            {
                sql += "AND u.fname_en LIKE '%" + obj.fname_en + "%' ";
            }
            if (obj.lname_en != null &&
               obj.lname_en.ToString() != null &&
               obj.lname_en.ToString() != "0")
            {
                sql += "AND u.lname_en LIKE '%" + obj.lname_en + "%' ";
            }
            if (obj.id_card != null &&
               obj.id_card.ToString() != null &&
               obj.id_card.ToString() != "0")
            {
                sql += "AND u.id_card LIKE '%" + obj.id_card + "%' ";
            }
            if (obj.tag != null &&
               obj.tag.ToString() != null &&
               obj.tag.ToString() != "0")
            {
                sql += "AND u.tag LIKE '%" + obj.tag + "%' ";
            }
            if (obj.birth_date != null &&
               obj.birth_date.ToString() != null &&
               obj.birth_date.ToString() != "0")
            {
                sql += "AND u.birth_date LIKE '%" + obj.birth_date + "%' ";
            }
            if (obj.face_image != null &&
               obj.face_image.ToString() != null &&
               obj.face_image.ToString() != "0")
            {
                sql += "AND u.face_image LIKE '%" + obj.face_image + "%' ";
            }
            if (obj.gender != null &&
               obj.gender.ToString() != null &&
               obj.gender.ToString() != "0")
            {
                sql += "AND u.gender LIKE '%" + obj.gender + "%' ";
            }
            if (obj.email != null &&
               obj.email.ToString() != null &&
               obj.email.ToString() != "0")
            {
                sql += "AND u.email LIKE '%" + obj.email + "%' ";
            }
            if (obj.telephone != null &&
               obj.telephone.ToString() != null &&
               obj.telephone.ToString() != "0")
            {
                sql += "AND u.telephone LIKE '%" + obj.telephone + "%' ";
            }
            if (obj.mobile != null &&
               obj.mobile.ToString() != null &&
               obj.mobile.ToString() != "0")
            {
                sql += "AND u.mobile LIKE '%" + obj.mobile + "%' ";
            }
            if (obj.role != null &&
               obj.role.ToString() != null &&
               obj.role.ToString() != "0")
            {
                sql += "AND u.role LIKE '%" + obj.role + "%' ";
            }
            if (obj.lang != null &&
               obj.lang.ToString() != null &&
               obj.lang.ToString() != "0")
            {
                sql += "AND u.lang LIKE '%" + obj.lang + "%' ";
            }
            if (obj.create_date != null &&
               obj.create_date.ToString() != null &&
               obj.create_date.ToString() != "0")
            {
                sql += "AND u.create_date LIKE '%" + obj.create_date + "%' ";
            }
            if (obj.create_by != null &&
               obj.create_by.ToString() != null &&
               obj.create_by.ToString() != "0")
            {
                sql += "AND u.create_by LIKE '%" + obj.create_by + "%' ";
            }
            if (obj.update_date != null &&
               obj.update_date.ToString() != null &&
               obj.update_date.ToString() != "0")
            {
                sql += "AND u.update_date LIKE '%" + obj.update_date + "%' ";
            }
            if (obj.update_by != null &&
               obj.update_by.ToString() != null &&
               obj.update_by.ToString() != "0")
            {
                sql += "AND u.update_by LIKE '%" + obj.update_by + "%' ";
            }
            if (obj.site_id != null &&
               obj.site_id.ToString() != null &&
               obj.site_id.ToString() != "0")
            {
                sql += "AND u.site_id LIKE '%" + obj.site_id + "%' ";
            }

            if (obj.user_campaign_id != null &&
          obj.user_campaign_id.ToString() != null &&
          obj.user_campaign_id.ToString() != "0")
            {
                sql += "AND uc.user_campaign_id LIKE '%" + obj.user_campaign_id + "%' ";
            }

            if (obj.product_id != null &&
               obj.product_id.ToString() != null &&
               obj.product_id.ToString() != "0")
            {
                sql += "AND uc.product_id LIKE '%" + obj.product_id + "%' ";
            }
            if (obj.campaign_id != null &&
               obj.campaign_id.ToString() != null &&
               obj.campaign_id.ToString() != "0")
            {
                sql += "AND uc.campaign_id LIKE '%" + obj.campaign_id + "%' ";
            }
            if (obj.member_type_id != null &&
               obj.member_type_id.ToString() != null &&
               obj.member_type_id.ToString() != "0")
            {
                sql += "AND uc.member_type_id LIKE '%" + obj.member_type_id + "%' ";
            }
            if (obj.payment_detail_id != null &&
               obj.payment_detail_id.ToString() != null &&
               obj.payment_detail_id.ToString() != "0")
            {
                sql += "AND uc.payment_detail_id LIKE '%" + obj.payment_detail_id + "%' ";
            }
            if (obj.barcode != null &&
               obj.barcode.ToString() != null &&
               obj.barcode.ToString() != "0")
            {
                sql += "AND uc.barcode LIKE '%" + obj.barcode + "%' ";
            }
            if (obj.RFID != null &&
               obj.RFID.ToString() != null &&
               obj.RFID.ToString() != "0")
            {
                sql += "AND uc.RFID LIKE '%" + obj.RFID + "%' ";
            }

            if (obj.reference_no != null &&
   obj.reference_no.ToString() != null &&
   obj.reference_no.ToString() != "0")
            {
                sql += "AND uc.reference_no LIKE '%" + obj.reference_no + "%' ";
            }

            if (obj.start_date != null &&
               obj.start_date.ToString() != null &&
               obj.start_date.ToString() != "0")
            {
                sql += "AND uc.start_date LIKE '%" + obj.start_date + "%' ";
            }
            if (obj.expire_date != null &&
               obj.expire_date.ToString() != null &&
               obj.expire_date.ToString() != "0")
            {
                sql += "AND uc.expire_date LIKE '%" + obj.expire_date + "%' ";
            }
            if (obj.status != null &&
               obj.status.ToString() != null &&
               obj.status.ToString() != "0")
            {
                sql += "AND uc.status LIKE '%" + obj.status + "%' ";
            }


            sql += "ORDER BY uc.user_campaign_id DESC ";
            dt = DB.ExecuteReader(sql);

            return dt;

        }



    }
}
